package serviceImpl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import dao.DepartmentDao;
import dao.EmployeeDao;
import daoImpl.DepartmentDaoImpl;
import daoImpl.EmployeeDaoImpl;
import dto.Page;
import entity.Department;
import entity.Employee;
import service.EmployeeService;
import utils.JdbcUtils;

public class EmployeeServiceImpl implements EmployeeService{

	EmployeeDao  employeeDao=new EmployeeDaoImpl();
	DepartmentDao departmentDao=new DepartmentDaoImpl();
	public void addEmployee(Employee employee)  {
			Connection connection=null;
			
		try {	
			connection = JdbcUtils.getConnection();
			connection.setAutoCommit(false);
			String sql="INSERT INTO employee(emp_name,dept_id,gender,email,phone,hire_date) VALUES(?,?,?,?,?,?)";
			employeeDao.addEmployee(connection, sql,employee.getEmpName(),employee.getDeptId(),employee.getGender(),employee.getEmail(),employee.getPhone(),employee.getHireDate());
			connection.commit();
		} catch (SQLException e) {
			if(connection!=null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}		
			}
			e.printStackTrace();
		}finally {
			
				JdbcUtils.close();		
		}
		
	}

	public void deleteEmployee(Integer empId){
		
		Connection connection=null;
		try {
			connection = JdbcUtils.getConnection();
			connection.setAutoCommit(false);
			String sql="delete from employee where emp_id=?";
			employeeDao.deleteEmployee(connection, sql, empId);
			connection.commit();
			
		} catch (SQLException e) {
			if(connection!=null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		}finally {
			JdbcUtils.close();
		}
		
		
		
		
	}

	public void updateEmployee(Employee employee) {
		Connection connection=null;
		
		try {	
			connection = JdbcUtils.getConnection();
			connection.setAutoCommit(false);
			String sql="UPDATE employee SET emp_name=?,dept_id=?,gender=?,email=?,phone=?,hire_date=? WHERE emp_id=?";
			employeeDao.updateEmployee(connection, sql, employee.getEmpName(),employee.getDeptId(),employee.getGender(),employee.getEmail(),employee.getPhone(),employee.getHireDate(),employee.getEmpId());
			connection.commit();
		} catch (SQLException e) {
			if(connection!=null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}		
			}
			e.printStackTrace();
		}finally {
			
				JdbcUtils.close();		
		}
		
	}

	public Employee getEmployeeByEmpId(Integer empId) {
		Connection connection=null;
		
		try {
			connection = JdbcUtils.getConnection();
			connection.setAutoCommit(false);
			String sql="SELECT emp_id as empId,emp_name as empName,gender,email,phone,hire_date as hireDate,dept_id as deptId FROM employee WHERE emp_id=?";
			Employee employee = employeeDao.getEmployeeByEmpId(connection, sql, Employee.class, empId);
			connection.commit();
			return employee;
		} catch (SQLException e) {
			if(connection!=null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		}finally {
			JdbcUtils.close();
		}
		
		return null;	
		
	}

	public Page getEmployeeList(Integer pageNo, Integer pageSize) {
		
		Connection connection=null;
		Page page=new Page();
		int offSet =(pageNo-1)*pageSize;
		
		try {
			connection = JdbcUtils.getConnection();
			connection.setAutoCommit(false);
			String sql="SELECT emp_id as empId,dept_id as deptId,emp_name as empName,gender,email,phone,hire_date as hireDate FROM employee LIMIT ?,?";	
			List<Employee> list = employeeDao.getEmployeeList(connection, sql, Employee.class, offSet,pageSize);
			
			String sql2="select dept_id as deptId,dept_name as deptName from department where dept_id=?";
			for(Employee employee:list) {
				
				Department department = departmentDao.getDepartmentBydeptId(connection, sql2, Department.class, employee.getDeptId());
				employee.setDepartment(department);		
			}
			
			String sql3="select count(*) as count from employee";
			Integer count = employeeDao.getCount(connection, sql3);
			
			page.setPageNo(pageNo);
			page.setPageSize(pageSize);
			page.setObj(list);
			
			int pageCount=(count%pageSize==0)?(count/pageSize):(count/pageSize+1);
			
			page.setPageCount(pageCount);
			
			if(pageNo>1) {
				page.setHasPre(true);
			}else {
				page.setHasPre(false);
			}
			
			if(pageNo<pageCount) {
				page.setHasNext(true);
			}else {
				page.setHasNext(false);
			}
			
			
			
			connection.commit();
			
		} catch (SQLException e) {
			if(connection!=null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		}finally {
			JdbcUtils.close();
		}
		
		return page;
		
		
		
		
	}

}
